package org.compiere.report;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAccount;
import org.compiere.model.MAssetAcct;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.X_A_Asset;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;


public class A_Asset_Form_INV3 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	int p_AD_Org_ID;
	int p_C_BPartnerSR_ID;
	
	String getSQlresult = null;

	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("AD_Org_ID")) 
				p_AD_Org_ID  = para[i].getParameterAsInt();
			else if (name.equals("C_BPartnerSR_ID")) 
				p_C_BPartnerSR_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "A_Asset_Form_INV1_tmp.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		Timestamp currdate = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
		StringBuffer sbuf = new StringBuffer();
		
		MOrg org = MOrg.get(m_ctx,p_AD_Org_ID);
		R = sheet.getRow(10);
		C = R.getCell(24);
		C.setCellValue(org.getName());
		MBPartner bpartner = MBPartner.get(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()));
		C = R.getCell(69);
		C.setCellValue(bpartner.get_Value("FI_TIN").toString());
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT A_Asset_ID "
				+ "FROM A_Asset "
				+ "WHERE AD_Client_ID=" + Env.getAD_Client_ID(m_ctx)
				+ " AND AD_Org_ID="+p_AD_Org_ID
				+ " AND A_Asset_Group_ID="+1000007
				+ " AND C_BPartnerSR_ID = "+p_C_BPartnerSR_ID
				+ " AND IsActive = 'Y'";
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int iAssetID=0, nn=0, row=37;
			while (rs.next())
			{
				sheet.shiftRows(row+1, 68, 1);
				nn++;row++;
				iAssetID = rs.getInt(1);
				X_A_Asset asset = new X_A_Asset (getCtx(), iAssetID, get_TrxName());
				
				R = sheet.getRow(row);
				
				for(int i=0;i<4;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region1 = new CellRangeAddress(row, row, 0, 3);
				sheet.addMergedRegion(region1);
				
				for(int i=4;i<16;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region2 = new CellRangeAddress(row, row, 4, 15);
				sheet.addMergedRegion(region2);
				
				for(int i=16;i<22;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region3 = new CellRangeAddress(row, row, 16, 21);
				sheet.addMergedRegion(region3);
				
				for(int i=22;i<28;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region4 = new CellRangeAddress(row, row, 22, 27);
				sheet.addMergedRegion(region4);
				
				for(int i=28;i<35;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region5 = new CellRangeAddress(row, row, 28, 34);
				sheet.addMergedRegion(region5);
				
				for(int i=35;i<43;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region6 = new CellRangeAddress(row, row, 35, 42);
				sheet.addMergedRegion(region6);
				
				for(int i=43;i<48;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region7 = new CellRangeAddress(row, row, 43, 47);
				sheet.addMergedRegion(region7);
				
				for(int i=48;i<54;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region8 = new CellRangeAddress(row, row, 48, 53);
				sheet.addMergedRegion(region8);
				
				for(int i=54;i<61;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region9 = new CellRangeAddress(row, row, 54, 60);
				sheet.addMergedRegion(region9);
				
				for(int i=61;i<67;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region10 = new CellRangeAddress(row, row, 61, 66);
				sheet.addMergedRegion(region10);
				
				for(int i=67;i<73;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region11 = new CellRangeAddress(row, row, 67, 72);
				sheet.addMergedRegion(region11);
				
				for(int i=73;i<=81;i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(37).getCell(0).getCellStyle());
				}
				CellRangeAddress region12 = new CellRangeAddress(row, row, 73, 81);
				sheet.addMergedRegion(region12);
				
				C = R.getCell(0);
				C.setCellValue(String.valueOf(nn));
				C = R.getCell(4);
				C.setCellValue(asset.getName());

				sbuf.setLength(0);
				sbuf.append("SELECT A_Asset_Acct_ID "
						+ "FROM A_Asset_Acct "
						+ "WHERE A_Asset_ID= " + asset.get_ID()
						+ "AND PostingType = 'A'"
						+ "AND IsActive = 'Y'");
				int iAcctID = DB.getSQLValue(get_TrxName(),sbuf.toString());
				
				if (iAcctID>0) {
					MAssetAcct assetacct = new MAssetAcct (getCtx(), iAcctID, get_TrxName());
					MAccount acct = MAccount.get (getCtx(), assetacct.getA_Asset_Acct());
					C = R.getCell(43);
					C.setCellValue(acct.getAccount().getValue());
				}
				else
					{
						C = R.getCell(43);
						C.setCellValue(" ");
					}
				
				sbuf.setLength(0);
				
				if (asset.getAssetServiceDate()!=null) {
					sbuf.append(dateFormat.format(asset.getAssetServiceDate()));
					C = R.getCell(35);
					C.setCellValue(sbuf.toString());
				}
				else
					{
						C = R.getCell(35);
						C.setCellValue(" ");
					}
				
				C = R.getCell(33);
				C.setCellValue(asset.getValue());
				C = R.getCell(54);
				C.setCellValue("1");
				
				BigDecimal bCost = asset.getAssetCost(currdate);
				BigDecimal bAccumDep = asset.getAccumDepreciation(currdate);
				if(bCost!=null) { 
					BigDecimal bDepCost=bCost.subtract(bAccumDep); 
					C = R.getCell(67);
					C.setCellValue(bDepCost.toString());
				}
				else
					{//sheet.addCell(new Label(67, row, "", cellstyle));
						C = R.getCell(67);
						C.setCellValue(" ");
					}
				//sheet.insertRow(row+1);
			}
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		try {
				//Forces Excel to use the specified file as a template for the default workbook. 
				Runtime.getRuntime().exec("cmd /c start excel " + file+" /t");
			} 
		catch (Exception e) 
			{
				e.printStackTrace();
			}

		return Msg.translate(m_ctx, "Success");
	}
}